/*
 * Created on Feb 9, 2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hezhi.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

/**
 * SQLite operator class
 *         
 */
public abstract class AbstractSQLiteDAO {
	
	static public Connection getDBConnection() throws SQLException {
		SQLiteConfig config = new SQLiteConfig();
		// config.setReadOnly(true);
		config.setSharedCache(true);
		config.enableRecursiveTriggers(true);

		SQLiteDataSource ds = new SQLiteDataSource(config);
		ds.setUrl("jdbc:sqlite:configuration/sample.db");
		return ds.getConnection();
	}

	// create Table
	public void createTable(String createTblSql) throws SQLException {
		String sql = "create table IF NOT EXISTS users (username string, password string,right string,filepath string); ";
		Statement stat = null;
		stat = AbstractSQLiteDAO.getDBConnection().createStatement();
		stat.executeUpdate(sql);

	}

	// drop table
	public void dropTable(String tableName) throws SQLException {
		String sql = "drop table " + tableName.trim();
		Statement stat = null;
		stat = AbstractSQLiteDAO.getDBConnection().createStatement();
		stat.executeUpdate(sql);
	}

	// 新增
	public void insert( int id, String name) throws SQLException {
		String sql = "insert into test (username,password,right,filepath) values(?,?,?,?)";
		PreparedStatement pst = null;
		pst = AbstractSQLiteDAO.getDBConnection().prepareStatement(sql);
		int idx = 1;
		pst.setInt(idx++, id);
		pst.setString(idx++, name);
		pst.executeUpdate();

	}

	// 修改
	public void update( int id, String username) throws SQLException {
		String sql = "update test set name = ? where username = ?";
		PreparedStatement pst = null;
		pst = AbstractSQLiteDAO.getDBConnection().prepareStatement(sql);
		int idx = 1;
		pst.setString(idx++, username);
		pst.setString(idx++, username);
		pst.executeUpdate();
	}

	// 刪除
	public void delete(Connection con, int id) throws SQLException {
		String sql = "delete from test where username = ?";
		PreparedStatement pst = null;
		pst = AbstractSQLiteDAO.getDBConnection().prepareStatement(sql);
		int idx = 1;
		pst.setInt(idx++, id);
		pst.executeUpdate();
	}

	public void selectAll(Connection con) throws SQLException {
		String sql = "select * from test";
		Statement stat = null;
		ResultSet rs = null;
		stat = AbstractSQLiteDAO.getDBConnection().createStatement();
		rs = stat.executeQuery(sql);
		while (rs.next()) {
			System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
		}
	}
	
	public static void closeConnection(final Connection conn)
	{
		if (conn != null)
		{
			try
			{
				conn.close();
			}
			catch (SQLException ex)
			{
				ex.printStackTrace();
				// log.error(conn, ex);
			}
		}
	}

	public static void closeStatement(final Statement stmt)
	{
		if (stmt != null)
		{
			try
			{
				stmt.close();
			}
			catch (SQLException ex)
			{
				// log.error(stmt, ex);
			}
		}
	}
	
	public static void closeResultSet(final ResultSet rs)
	{
		if (rs != null)
		{
			try
			{
				rs.close();
			}
			catch (SQLException ex)
			{
				// log.error(rs, ex);
			}
		}
	}
	
	public static void closeAllResource(ResultSet rs, Statement stmt, Connection conn)
	{
		closeResultSet(rs);
		closeStatement(stmt);
		closeConnection(conn);
	}
}
